/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ptit.classlearning;

/**
 *
 * @author Pretty
 */
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSetMetaData;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JOptionPane;

/**
 *
 * @author Admin
 */
public class ConClass {
    static Connection conn =null;
    static Statement stm;
    private  String name;    
    private String classID;
    private String major;    
    
    public ConClass()
    {
        
    }
    public ConClass(String name,String major)
    {
        this.name=name;
        this.major=major;        
    }
    public void setClassID(String classID)
    {
        this.classID = classID;
    }
    public String getClassID()
    {
        return classID;
    }
    
    public  void connect(){
        try{
        String userConn ="root";
        String passConn = "tyty2629";
        String url = "jdbc:mysql://localhost:3306/mangementscore";
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        conn =  (Connection) DriverManager.getConnection(url, userConn, passConn);
        System.out.println("Da ket noi CSDL");
        stm = (Statement) conn.createStatement();
        }
        catch(Exception e)
        {
        System.out.println(e.toString());
         }
    }

    public  void disconnect() throws SQLException  {
        stm.close();
        conn.close();
    }
    public void addTitle(StringBuilder sb)
    {
             sb.append("<tr>");
            sb.append("<th>");
            sb.append("Lớp");
            sb.append("</th>");
            sb.append("<th>");
            sb.append("Ngành");
            sb.append("</th>");
            sb.append("<th>");
            sb.append("Sỉ số");
            sb.append("</th>"); 
                    
            sb.append("<th>");
            sb.append("Xóa");
            sb.append("</th>");
            sb.append("<th>");
            sb.append("Sửa");
            sb.append("</th>");
            sb.append("</tr>");
    }
    public String showAll()
    {
       String allClasses = "";
        StringBuilder sb = new StringBuilder();
        try {
            ResultSet rs = stm.executeQuery("select * from class");
            ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
            sb.append("<table border>");
            sb.append("<tr>");
            addTitle(sb);
           


            while (rs.next()) {
                sb.append("<tr>");
                for (int i = 1; i < rsmd.getColumnCount(); i++) {
                    sb.append("<td>");
                    sb.append(rs.getString(i + 1));
                    sb.append("</td>");                    
                }
                sb.append("<td>");
                sb.append(selectCapacity(rs.getString(1)));    
                 sb.append("</td>");    
                sb.append("<td><a href=\"Controller?requestAction=deleteClass#classID=")
                         .append(rs.getString(1)).append("\"><img src=\"Images/Delete32.png\"></a></td>");       
                 sb.append("<td><a href=\"EditClass.jsp?class_name=")
                         .append(rs.getString(2)).append("#major=").append(rs.getString(3))
                            .append("#classID=").append(rs.getInt(1))
                         .append("\"><img src=\"Images/Student_edit32.png\"></a></td>");
                sb.append("</tr>");
            }

            sb.append("</table>");
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }
        allClasses = sb.toString();        
        return allClasses;
    }

    public void addClass() throws SQLException {
     connect();
    String cmdInsert = "INSERT INTO  `mangementscore`.`class` (class_name,major) VALUES ("
             + "'" +name + "'" + "," 
            + "'" + major + "'" +");";
    
    stm.executeUpdate(cmdInsert);
    disconnect();
    }
    public void deleteClass(String classID) throws SQLException
    {
        connect();
        String cmdDelete = "Delete from class where class_id =  '"+classID+"';";
        stm.executeUpdate(cmdDelete);
        disconnect();
    }

    public String showClassName() {
        StringBuilder sb = new StringBuilder();
        String cmdSelect="Select class_name from class";
         try {
            ResultSet rs = stm.executeQuery(cmdSelect);
              while (rs.next()) {
                  sb.append("<option>");
                  sb.append(rs.getString(1));
              }
         }
         catch(Exception e)
         {
            return "Lỗi khi lấy menu" ;
         }
         return sb.toString();
    }

    public String selectClassID(String classLearning)  {
     
        String cmdSelect="Select class_id from class where class_name = '" + classLearning+"';";
        String classIDSelect="" ;
        try{ 
            connect();
            ResultSet rs = stm.executeQuery(cmdSelect);
              while (rs.next()) {
                  classIDSelect = rs.getString(1);
              }
              disconnect();
                }
         catch(Exception e)
         {
     JOptionPane.showMessageDialog(null, e.getMessage());
         }
        
              return classIDSelect;
    }
    
    public  int selectCapacity(String idSelect) throws SQLException
    {
        ResultSet rsCapacity=null;
        try{
           Statement stmClassName = (Statement) conn.createStatement();
          String cmdSelect = "Select count(*) from student where class_id = '"+ idSelect+"';";
            rsCapacity= stmClassName.executeQuery(cmdSelect);
            rsCapacity.next();   
          
        }catch(SQLException e)
        {
                 JOptionPane.showMessageDialog(null, e.getMessage());
        }
          return rsCapacity.getInt(1);
    }

    public void editClass() throws SQLException {
        
         
         String classIdEdit = classID;
        
         connect();
        String cmdUpdate ="UPDATE `mangementscore`. `class` SET   class_name= '"  + name     
                + "',major='" + major                  
                +"'  where  class_id LIKE "+ classIdEdit.trim()+";";
        stm.executeUpdate(cmdUpdate);
        disconnect();
    }
}
